using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using hao_Common.Dapper.Db;
using hao_Dto;
using hao_Model;
using hao_Model.Enums;

namespace hao_Reppsitory.Order
{
    /// <summary>
    /// 订单仓储实现
    /// </summary>
    public class OrderRepository : IOrderRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public OrderRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<Guid> AddAsync(hao_Model.Order order)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                INSERT INTO Orders (
                    Id, OrderNumber, BuyerId, SellerId, ProductId, ProductTitle, ProductPrice,
                    FinalPrice, Quantity, TotalAmount, DeliveryMethod, Status, PaymentStatus,
                    DeliveryStatus, TrackingNumber, BuyerAddressId, BuyerMessage, SellerMessage,
                    CreatedAt, UpdatedAt, PaidAt, ShippedAt, DeliveredAt, CompletedAt, 
                    CancelledAt, CancelReason, IsDeleted
                ) VALUES (
                    @Id, @OrderNumber, @BuyerId, @SellerId, @ProductId, @ProductTitle, @ProductPrice,
                    @FinalPrice, @Quantity, @TotalAmount, @DeliveryMethod, @Status, @PaymentStatus,
                    @DeliveryStatus, @TrackingNumber, @BuyerAddressId, @BuyerMessage, @SellerMessage,
                    @CreatedAt, @UpdatedAt, @PaidAt, @ShippedAt, @DeliveredAt, @CompletedAt,
                    @CancelledAt, @CancelReason, @IsDeleted
                )";
            
            await connection.ExecuteAsync(sql, order);
            return order.Id;
        }

        public async Task<hao_Model.Order> GetByIdAsync(Guid id)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT * FROM Orders 
                WHERE Id = @Id AND IsDeleted = 0";
            
            return await connection.QueryFirstOrDefaultAsync<hao_Model.Order>(sql, new { Id = id });
        }

        public async Task<hao_Model.Order> GetByOrderNumberAsync(string orderNumber)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT * FROM Orders 
                WHERE OrderNumber = @OrderNumber AND IsDeleted = 0";
            
            return await connection.QueryFirstOrDefaultAsync<hao_Model.Order>(sql, new { OrderNumber = orderNumber });
        }

        public async Task<bool> UpdateAsync(hao_Model.Order order)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE Orders SET 
                    FinalPrice = @FinalPrice,
                    Quantity = @Quantity,
                    TotalAmount = @TotalAmount,
                    DeliveryMethod = @DeliveryMethod,
                    Status = @Status,
                    PaymentStatus = @PaymentStatus,
                    DeliveryStatus = @DeliveryStatus,
                    TrackingNumber = @TrackingNumber,
                    BuyerAddressId = @BuyerAddressId,
                    BuyerMessage = @BuyerMessage,
                    SellerMessage = @SellerMessage,
                    UpdatedAt = @UpdatedAt,
                    PaidAt = @PaidAt,
                    ShippedAt = @ShippedAt,
                    DeliveredAt = @DeliveredAt,
                    CompletedAt = @CompletedAt,
                    CancelledAt = @CancelledAt,
                    CancelReason = @CancelReason
                WHERE Id = @Id AND IsDeleted = 0";
            
            var result = await connection.ExecuteAsync(sql, order);
            return result > 0;
        }

        public async Task<bool> DeleteAsync(Guid id)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE Orders 
                SET IsDeleted = 1, UpdatedAt = @UpdatedAt 
                WHERE Id = @Id";
            
            var result = await connection.ExecuteAsync(sql, new { Id = id, UpdatedAt = DateTime.UtcNow });
            return result > 0;
        }

        public async Task<(List<hao_Model.Order> orders, int total)> SearchOrdersAsync(OrderSearchDto searchDto)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string> { "IsDeleted = 0" };
            var parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(searchDto.OrderNumber))
            {
                whereConditions.Add("OrderNumber LIKE @OrderNumber");
                parameters.Add("OrderNumber", $"%{searchDto.OrderNumber}%");
            }

            if (searchDto.BuyerId.HasValue)
            {
                whereConditions.Add("BuyerId = @BuyerId");
                parameters.Add("BuyerId", searchDto.BuyerId.Value);
            }

            if (searchDto.SellerId.HasValue)
            {
                whereConditions.Add("SellerId = @SellerId");
                parameters.Add("SellerId", searchDto.SellerId.Value);
            }

            if (searchDto.ProductId.HasValue)
            {
                whereConditions.Add("ProductId = @ProductId");
                parameters.Add("ProductId", searchDto.ProductId.Value);
            }

            if (searchDto.Status.HasValue)
            {
                whereConditions.Add("Status = @Status");
                parameters.Add("Status", (int)searchDto.Status.Value);
            }

            if (searchDto.PaymentStatus.HasValue)
            {
                whereConditions.Add("PaymentStatus = @PaymentStatus");
                parameters.Add("PaymentStatus", (int)searchDto.PaymentStatus.Value);
            }

            if (searchDto.StartDate.HasValue)
            {
                whereConditions.Add("CreatedAt >= @StartDate");
                parameters.Add("StartDate", searchDto.StartDate.Value);
            }

            if (searchDto.EndDate.HasValue)
            {
                whereConditions.Add("CreatedAt <= @EndDate");
                parameters.Add("EndDate", searchDto.EndDate.Value);
            }

            if (!string.IsNullOrWhiteSpace(searchDto.Keyword))
            {
                whereConditions.Add("(ProductTitle LIKE @Keyword OR BuyerMessage LIKE @Keyword OR SellerMessage LIKE @Keyword)");
                parameters.Add("Keyword", $"%{searchDto.Keyword}%");
            }

            var whereClause = string.Join(" AND ", whereConditions);
            var orderClause = $"ORDER BY {searchDto.SortBy} {(searchDto.SortDesc ? "DESC" : "ASC")}";

            // 查询总数
            var countSql = $"SELECT COUNT(*) FROM Orders WHERE {whereClause}";
            var total = await connection.QuerySingleAsync<int>(countSql, parameters);

            // 查询数据
            var offset = (searchDto.PageNumber - 1) * searchDto.PageSize;
            var dataSql = $@"
                SELECT * FROM Orders 
                WHERE {whereClause} 
                {orderClause} 
                LIMIT @PageSize OFFSET @Offset";
            
            parameters.Add("PageSize", searchDto.PageSize);
            parameters.Add("Offset", offset);

            var orders = await connection.QueryAsync<hao_Model.Order>(dataSql, parameters);
            return (orders.ToList(), total);
        }

        public async Task<List<hao_Model.Order>> GetBuyerOrdersAsync(Guid buyerId, OrderStatus? status = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "SELECT * FROM Orders WHERE BuyerId = @BuyerId AND IsDeleted = 0";
            var parameters = new DynamicParameters();
            parameters.Add("BuyerId", buyerId);

            if (status.HasValue)
            {
                sql += " AND Status = @Status";
                parameters.Add("Status", (int)status.Value);
            }

            sql += " ORDER BY CreatedAt DESC";
            
            var result = await connection.QueryAsync<hao_Model.Order>(sql, parameters);
            return result.ToList();
        }

        public async Task<List<hao_Model.Order>> GetSellerOrdersAsync(Guid sellerId, OrderStatus? status = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "SELECT * FROM Orders WHERE SellerId = @SellerId AND IsDeleted = 0";
            var parameters = new DynamicParameters();
            parameters.Add("SellerId", sellerId);

            if (status.HasValue)
            {
                sql += " AND Status = @Status";
                parameters.Add("Status", (int)status.Value);
            }

            sql += " ORDER BY CreatedAt DESC";
            
            var result = await connection.QueryAsync<hao_Model.Order>(sql, parameters);
            return result.ToList();
        }

        public async Task<List<hao_Model.Order>> GetProductOrdersAsync(Guid productId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT * FROM Orders 
                WHERE ProductId = @ProductId AND IsDeleted = 0 
                ORDER BY CreatedAt DESC";
            
            var result = await connection.QueryAsync<hao_Model.Order>(sql, new { ProductId = productId });
            return result.ToList();
        }

        public async Task<bool> UpdateOrderStatusAsync(Guid orderId, OrderStatus status, Guid? operatorId = null, string reason = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var transaction = connection.BeginTransaction();
            
            try
            {
                // 更新订单状态
                const string updateSql = @"
                    UPDATE Orders 
                    SET Status = @Status, UpdatedAt = @UpdatedAt 
                    WHERE Id = @OrderId";
                
                await connection.ExecuteAsync(updateSql, 
                    new { Status = (int)status, UpdatedAt = DateTime.UtcNow, OrderId = orderId }, transaction);

                // 记录状态变更日志
                const string logSql = @"
                    INSERT INTO OrderStatusLogs (Id, OrderId, ToStatus, OperatorId, OperatorType, Reason, CreatedAt)
                    VALUES (@Id, @OrderId, @ToStatus, @OperatorId, @OperatorType, @Reason, @CreatedAt)";
                
                await connection.ExecuteAsync(logSql, new
                {
                    Id = Guid.NewGuid(),
                    OrderId = orderId,
                    ToStatus = (int)status,
                    OperatorId = operatorId,
                    OperatorType = operatorId.HasValue ? (int)OperatorType.System : (int)OperatorType.System,
                    Reason = reason,
                    CreatedAt = DateTime.UtcNow
                }, transaction);

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public async Task<bool> UpdatePaymentStatusAsync(Guid orderId, PaymentStatus status, DateTime? paidAt = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE Orders 
                SET PaymentStatus = @PaymentStatus, PaidAt = @PaidAt, UpdatedAt = @UpdatedAt 
                WHERE Id = @OrderId";
            
            var result = await connection.ExecuteAsync(sql, new
            {
                PaymentStatus = (int)status,
                PaidAt = paidAt,
                UpdatedAt = DateTime.UtcNow,
                OrderId = orderId
            });
            
            return result > 0;
        }

        public async Task<bool> UpdateDeliveryStatusAsync(Guid orderId, DeliveryStatus status, DateTime? shippedAt = null, DateTime? deliveredAt = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE Orders 
                SET DeliveryStatus = @DeliveryStatus, ShippedAt = @ShippedAt, DeliveredAt = @DeliveredAt, UpdatedAt = @UpdatedAt 
                WHERE Id = @OrderId";
            
            var result = await connection.ExecuteAsync(sql, new
            {
                DeliveryStatus = (int)status,
                ShippedAt = shippedAt,
                DeliveredAt = deliveredAt,
                UpdatedAt = DateTime.UtcNow,
                OrderId = orderId
            });
            
            return result > 0;
        }

        public async Task<OrderStatisticsDto> GetOrderStatisticsAsync(Guid? userId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            var whereClause = "IsDeleted = 0";
            var parameters = new DynamicParameters();
            
            if (userId.HasValue)
            {
                whereClause += " AND (BuyerId = @UserId OR SellerId = @UserId)";
                parameters.Add("UserId", userId.Value);
            }

            var sql = $@"
                SELECT 
                    COUNT(*) as TotalOrders,
                    COUNT(CASE WHEN Status IN (1,2,3,4,5,6) THEN 1 END) as PendingOrders,
                    COUNT(CASE WHEN Status = 7 THEN 1 END) as CompletedOrders,
                    COUNT(CASE WHEN Status = 8 THEN 1 END) as CancelledOrders,
                    COALESCE(SUM(TotalAmount), 0) as TotalAmount,
                    COALESCE(SUM(CASE WHEN DATE(CreatedAt) = CURDATE() THEN TotalAmount ELSE 0 END), 0) as TodayAmount,
                    COALESCE(SUM(CASE WHEN YEAR(CreatedAt) = YEAR(CURDATE()) AND MONTH(CreatedAt) = MONTH(CURDATE()) THEN TotalAmount ELSE 0 END), 0) as MonthAmount
                FROM Orders 
                WHERE {whereClause}";

            return await connection.QueryFirstOrDefaultAsync<OrderStatisticsDto>(sql, parameters);
        }

        public async Task<List<OrderDailyStatsDto>> GetDailyOrderStatsAsync(DateTime startDate, DateTime endDate, Guid? userId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            var whereClause = "IsDeleted = 0 AND DATE(CreatedAt) BETWEEN @StartDate AND @EndDate";
            var parameters = new DynamicParameters();
            parameters.Add("StartDate", startDate.Date);
            parameters.Add("EndDate", endDate.Date);
            
            if (userId.HasValue)
            {
                whereClause += " AND (BuyerId = @UserId OR SellerId = @UserId)";
                parameters.Add("UserId", userId.Value);
            }

            var sql = $@"
                SELECT 
                    DATE(CreatedAt) as Date,
                    COUNT(*) as OrderCount,
                    COALESCE(SUM(TotalAmount), 0) as TotalAmount,
                    COUNT(CASE WHEN Status = 7 THEN 1 END) as CompletedCount,
                    COUNT(CASE WHEN Status = 8 THEN 1 END) as CancelledCount
                FROM Orders 
                WHERE {whereClause}
                GROUP BY DATE(CreatedAt)
                ORDER BY Date";

            var result = await connection.QueryAsync<OrderDailyStatsDto>(sql, parameters);
            return result.ToList();
        }

        public async Task<int> GetOrderCountByStatusAsync(OrderStatus status, Guid? userId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            var whereClause = "IsDeleted = 0 AND Status = @Status";
            var parameters = new DynamicParameters();
            parameters.Add("Status", (int)status);
            
            if (userId.HasValue)
            {
                whereClause += " AND (BuyerId = @UserId OR SellerId = @UserId)";
                parameters.Add("UserId", userId.Value);
            }

            var sql = $"SELECT COUNT(*) FROM Orders WHERE {whereClause}";
            return await connection.QuerySingleAsync<int>(sql, parameters);
        }

        public async Task<decimal> GetTotalAmountAsync(DateTime? startDate = null, DateTime? endDate = null, Guid? userId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            var whereConditions = new List<string> { "IsDeleted = 0", "Status = 7" }; // 只统计已完成订单
            var parameters = new DynamicParameters();
            
            if (startDate.HasValue)
            {
                whereConditions.Add("CreatedAt >= @StartDate");
                parameters.Add("StartDate", startDate.Value);
            }
            
            if (endDate.HasValue)
            {
                whereConditions.Add("CreatedAt <= @EndDate");
                parameters.Add("EndDate", endDate.Value);
            }
            
            if (userId.HasValue)
            {
                whereConditions.Add("(BuyerId = @UserId OR SellerId = @UserId)");
                parameters.Add("UserId", userId.Value);
            }

            var whereClause = string.Join(" AND ", whereConditions);
            var sql = $"SELECT COALESCE(SUM(TotalAmount), 0) FROM Orders WHERE {whereClause}";
            
            return await connection.QuerySingleAsync<decimal>(sql, parameters);
        }

        public async Task<bool> CheckOrderOwnershipAsync(Guid orderId, Guid userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT COUNT(*) FROM Orders 
                WHERE Id = @OrderId AND (BuyerId = @UserId OR SellerId = @UserId) AND IsDeleted = 0";
            
            var count = await connection.QuerySingleAsync<int>(sql, new { OrderId = orderId, UserId = userId });
            return count > 0;
        }

        public async Task<bool> CanCancelOrderAsync(Guid orderId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = "SELECT Status FROM Orders WHERE Id = @OrderId AND IsDeleted = 0";
            var status = await connection.QueryFirstOrDefaultAsync<OrderStatus?>(sql, new { OrderId = orderId });
            
            return status.HasValue && status.Value == OrderStatus.PendingSellerConfirm;
        }

        public async Task<bool> CanConfirmOrderAsync(Guid orderId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = "SELECT Status FROM Orders WHERE Id = @OrderId AND IsDeleted = 0";
            var status = await connection.QueryFirstOrDefaultAsync<OrderStatus?>(sql, new { OrderId = orderId });
            
            return status.HasValue && status.Value == OrderStatus.PendingDelivery;
        }

        public async Task<string> GenerateOrderNumberAsync()
        {
            // 生成格式：yyyyMMddHHmmss + 4位随机数
            var timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
            var random = new Random().Next(1000, 9999);
            var orderNumber = $"{timestamp}{random}";
            
            // 检查是否重复
            using var connection = _connectionFactory.CreateConnection();
            const string sql = "SELECT COUNT(*) FROM Orders WHERE OrderNumber = @OrderNumber";
            var count = await connection.QuerySingleAsync<int>(sql, new { OrderNumber = orderNumber });
            
            if (count > 0)
            {
                // 如果重复，递归生成新的订单号
                return await GenerateOrderNumberAsync();
            }
            
            return orderNumber;
        }
    }
}